--POUR CONTROLE


define erp='erp_mli2.';
define outils='outils_mli2.';

DROP TABLE se_gam_stdent;

CREATE TABLE se_gam_stdent AS (SELECT * FROM &erp.gam_stdent WHERE 1=2);


--SELECT * FROM UV_PENT_GAMMES_emploi e  /*gamme standard entete*/
--SELECT * FROM UV_PDET_GAMMES d  /*gamme standard ligne*/
--SELECT * FROM  UV_PPT_GAMMES  /*gamme entete article*/
--SELECT * FROM UV_PPT_GAMMES_DETGAM where id like '%P10832741094%' ORDER BY 1/*gamme ligne article */




 INSERT INTO se_gam_stdent

    (SELECT ge.id stdent_cod,
            '0' stdent_ind,
            null enreg_id,
            'N' enreg_s,
            SubStr(Nvl(ge.libelle,'.'),0,30) stdent_lib,
            null stdent_lib2,
            To_Date('31/12/1967','dd/mm/yyyy')+Nvl(ge.datcre,1) deb_dat,
            null fin_dat,
            'N' editlib2_ok,
            plan fit_cod,
            class classement_cod

        FROM UV_PENT_GAMMES ge--,UV_PDET_GAMMES gl
        --WHERE ge.id=gl.nogamme
        WHERE EXISTS (SELECT 1 FROM UV_PDET_GAMMES std_lig, UV_PPT_GAMMES_DETGAM art_lig
                      WHERE std_lig.nogamme=ge.id AND art_lig.detgam=std_lig.id)
        ) ;

 UPDATE se_gam_stdent e SET STDENT_LIB= (SELECT STDENT_LIB FROM &erp.gam_stdent se WHERE se.stdent_cod=e.stdent_cod AND se.stdent_ind=e.stdent_ind)
                        WHERE STDENT_LIB = '.' ;

 UPDATE se_gam_stdent tf SET enreg_id=(select enreg_id FROM &erp.gam_stdent f WHERE f.stdent_cod=tf.stdent_cod AND f.stdent_ind=tf.stdent_ind ) ;
 UPDATE se_gam_stdent tf SET enreg_id=&outils.enreg_id.NEXTVAL WHERE enreg_id IS NULL OR enreg_id =0;



 CALL uvse_synchroverserp('stdent_cod@stdent_ind','GAM_STDENT','O') ;

 commit;
 
 
select * from (
SELECT &outils.stragg(source) source,stdent_cod, stdent_ind, enreg_id, enreg_s, stdent_lib, stdent_lib2, deb_dat, fin_dat, editlib2_ok, fit_cod, classement_cod
FROM (
  SELECT 'uv' source ,g.* FROM se_gam_stdent g
  UNION ALL
  SELECT 'se' source ,g.* FROM &erp.gam_stdent g --where gene_cod ='P10832741094'

  )
GROUP BY  stdent_cod, stdent_ind, enreg_id, enreg_s, stdent_lib, stdent_lib2, deb_dat, fin_dat, editlib2_ok, fit_cod, classement_cod
HAVING Count(1)!=2) r
ORDER BY  stdent_cod, stdent_ind ;